import pymysql

from src.db import db


class DatabaseOperate(object):

    @classmethod
    def get_total(cls, database, name) -> int:
        query = f"SELECT COUNT(*) as count FROM {database}.{name}"
        try:
            cursor = db.connection.cursor()
            cursor.execute(query)
            total = cursor.fetchone()['count']
            return total
        except pymysql.MySQLError as e:
            print(f'Error executing query: {e}')
            return 0

    @classmethod
    def get_comment(cls, database, name):
        query = ("SELECT COLUMN_NAME, COLUMN_COMMENT FROM information_schema.COLUMNS"
                 " WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s")
        try:
            cursor = db.connection.cursor()
            cursor.execute(query, (database, name))
            columns = cursor.fetchall()
            headers = {col['COLUMN_NAME']: col['COLUMN_COMMENT'] for col in columns}
            return headers
        except pymysql.MySQLError as e:
            print(f'Error executing query: {e}')

    @classmethod
    def select_by_offset_limit(cls, database, name, limit, offset):
        try:
            query = f'SELECT * FROM {database}.{name} ORDER BY gkxx_gdrq LIMIT {limit} OFFSET {offset}'
            cursor = db.connection.cursor()
            cursor.execute(query)
            rows = cursor.fetchall()
            return rows
        except pymysql.MySQLError as e:
            print(f'Error executing query: {e}')
            return None

    @staticmethod
    def select_by_conditions(database, name, conditions: dict):
        query = f"SELECT * FROM {database}.{name} WHERE "
        query += " AND ".join([f"{key}=%s" for key in conditions.keys()])
        values = list(conditions.values())
        cursor = db.connection.cursor()
        try:
            cursor.execute(query, values)
            result = cursor.fetchall()
        except pymysql.MySQLError as e:
            print(f'Error executing query: {e}')
            return None
        return result
